import sqlite3 from 'sqlite3';
import { promisify } from 'util';
import path from 'path';
import fs from 'fs';

// 数据库配置
const DB_PATH = process.env.DATABASE_PATH || path.join(process.cwd(), 'data', 'flowise.db');

// 确保数据目录存在
const dataDir = path.dirname(DB_PATH);
if (!fs.existsSync(dataDir)) {
  fs.mkdirSync(dataDir, { recursive: true });
}

// 创建数据库连接
const db = new sqlite3.Database(DB_PATH);

// 将回调风格的 API 转换为 Promise
const dbRun = promisify(db.run.bind(db));
const dbGet = promisify(db.get.bind(db));
const dbAll = promisify(db.all.bind(db));

// 数据库初始化
export async function initializeDatabase() {
  try {
    // 创建会话表
    await dbRun(`
      CREATE TABLE IF NOT EXISTS sessions (
        id TEXT PRIMARY KEY,
        chatflow_id TEXT NOT NULL,
        user_id TEXT,
        start_time TEXT NOT NULL,
        end_time TEXT,
        message_count INTEGER DEFAULT 0,
        status TEXT DEFAULT 'active',
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // 创建消息表
    await dbRun(`
      CREATE TABLE IF NOT EXISTS messages (
        id TEXT PRIMARY KEY,
        session_id TEXT NOT NULL,
        role TEXT NOT NULL,
        content TEXT NOT NULL,
        timestamp TEXT NOT NULL,
        metadata TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (session_id) REFERENCES sessions (id)
      )
    `);

    // 创建执行追踪表
    await dbRun(`
      CREATE TABLE IF NOT EXISTS execution_traces (
        id TEXT PRIMARY KEY,
        session_id TEXT NOT NULL,
        step_name TEXT NOT NULL,
        step_type TEXT NOT NULL,
        input_data TEXT,
        output_data TEXT,
        status TEXT DEFAULT 'pending',
        error_message TEXT,
        duration INTEGER,
        timestamp TEXT NOT NULL,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (session_id) REFERENCES sessions (id)
      )
    `);

    // 创建索引
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_sessions_chatflow_id ON sessions (chatflow_id)`);
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions (user_id)`);
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_sessions_status ON sessions (status)`);
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages (session_id)`);
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON messages (timestamp)`);
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_traces_session_id ON execution_traces (session_id)`);
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_traces_timestamp ON execution_traces (timestamp)`);

    console.log('数据库初始化完成');
  } catch (error) {
    console.error('数据库初始化失败:', error);
    throw error;
  }
}

// 会话管理
export interface Session {
  id: string;
  chatflowId: string;
  userId?: string;
  startTime: string;
  endTime?: string;
  messageCount: number;
  status: 'active' | 'completed' | 'error';
  createdAt: string;
  updatedAt: string;
}

export async function createSession(session: Omit<Session, 'createdAt' | 'updatedAt'>): Promise<void> {
  await dbRun(`
    INSERT INTO sessions (id, chatflow_id, user_id, start_time, end_time, message_count, status)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `, [
    session.id,
    session.chatflowId,
    session.userId || null,
    session.startTime,
    session.endTime || null,
    session.messageCount,
    session.status
  ]);
}

export async function getSession(sessionId: string): Promise<Session | null> {
  const row = await dbGet(`
    SELECT * FROM sessions WHERE id = ?
  `, [sessionId]) as any;

  if (!row) return null;

  return {
    id: row.id,
    chatflowId: row.chatflow_id,
    userId: row.user_id,
    startTime: row.start_time,
    endTime: row.end_time,
    messageCount: row.message_count,
    status: row.status,
    createdAt: row.created_at,
    updatedAt: row.updated_at
  };
}

export async function updateSession(sessionId: string, updates: Partial<Session>): Promise<void> {
  const fields = [];
  const values = [];

  if (updates.endTime !== undefined) {
    fields.push('end_time = ?');
    values.push(updates.endTime);
  }
  if (updates.messageCount !== undefined) {
    fields.push('message_count = ?');
    values.push(updates.messageCount);
  }
  if (updates.status !== undefined) {
    fields.push('status = ?');
    values.push(updates.status);
  }

  if (fields.length > 0) {
    fields.push('updated_at = CURRENT_TIMESTAMP');
    values.push(sessionId);

    await dbRun(`
      UPDATE sessions SET ${fields.join(', ')} WHERE id = ?
    `, values);
  }
}

export async function getSessions(chatflowId?: string, userId?: string, status?: string): Promise<Session[]> {
  let query = 'SELECT * FROM sessions WHERE 1=1';
  const params = [];

  if (chatflowId) {
    query += ' AND chatflow_id = ?';
    params.push(chatflowId);
  }
  if (userId) {
    query += ' AND user_id = ?';
    params.push(userId);
  }
  if (status) {
    query += ' AND status = ?';
    params.push(status);
  }

  query += ' ORDER BY created_at DESC';

  const rows = await dbAll(query, params) as any[];

  return rows.map(row => ({
    id: row.id,
    chatflowId: row.chatflow_id,
    userId: row.user_id,
    startTime: row.start_time,
    endTime: row.end_time,
    messageCount: row.message_count,
    status: row.status,
    createdAt: row.created_at,
    updatedAt: row.updated_at
  }));
}

// 消息管理
export interface Message {
  id: string;
  sessionId: string;
  role: 'user' | 'assistant' | 'system';
  content: string;
  timestamp: string;
  metadata?: any;
  createdAt: string;
}

export async function createMessage(message: Omit<Message, 'createdAt'>): Promise<void> {
  await dbRun(`
    INSERT INTO messages (id, session_id, role, content, timestamp, metadata)
    VALUES (?, ?, ?, ?, ?, ?)
  `, [
    message.id,
    message.sessionId,
    message.role,
    message.content,
    message.timestamp,
    message.metadata ? JSON.stringify(message.metadata) : null
  ]);

  // 更新会话的消息计数
  await dbRun(`
    UPDATE sessions 
    SET message_count = message_count + 1, updated_at = CURRENT_TIMESTAMP 
    WHERE id = ?
  `, [message.sessionId]);
}

export async function getMessages(sessionId: string): Promise<Message[]> {
  const rows = await dbAll(`
    SELECT * FROM messages WHERE session_id = ? ORDER BY timestamp ASC
  `, [sessionId]) as any[];

  return rows.map(row => ({
    id: row.id,
    sessionId: row.session_id,
    role: row.role,
    content: row.content,
    timestamp: row.timestamp,
    metadata: row.metadata ? JSON.parse(row.metadata) : null,
    createdAt: row.created_at
  }));
}

// 执行追踪管理
export interface ExecutionTrace {
  id: string;
  sessionId: string;
  stepName: string;
  stepType: string;
  inputData?: any;
  outputData?: any;
  status: 'pending' | 'running' | 'completed' | 'error';
  errorMessage?: string;
  duration?: number;
  timestamp: string;
  createdAt: string;
}

export async function createExecutionTrace(trace: Omit<ExecutionTrace, 'createdAt'>): Promise<void> {
  await dbRun(`
    INSERT INTO execution_traces (id, session_id, step_name, step_type, input_data, output_data, status, error_message, duration, timestamp)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `, [
    trace.id,
    trace.sessionId,
    trace.stepName,
    trace.stepType,
    trace.inputData ? JSON.stringify(trace.inputData) : null,
    trace.outputData ? JSON.stringify(trace.outputData) : null,
    trace.status,
    trace.errorMessage || null,
    trace.duration || null,
    trace.timestamp
  ]);
}

export async function getExecutionTraces(sessionId: string): Promise<ExecutionTrace[]> {
  const rows = await dbAll(`
    SELECT * FROM execution_traces WHERE session_id = ? ORDER BY timestamp ASC
  `, [sessionId]) as any[];

  return rows.map(row => ({
    id: row.id,
    sessionId: row.session_id,
    stepName: row.step_name,
    stepType: row.step_type,
    inputData: row.input_data ? JSON.parse(row.input_data) : null,
    outputData: row.output_data ? JSON.parse(row.output_data) : null,
    status: row.status,
    errorMessage: row.error_message,
    duration: row.duration,
    timestamp: row.timestamp,
    createdAt: row.created_at
  }));
}

// 关闭数据库连接
export function closeDatabase(): Promise<void> {
  return new Promise((resolve, reject) => {
    db.close((err) => {
      if (err) {
        reject(err);
      } else {
        resolve();
      }
    });
  });
}

// 导出数据库实例（用于直接操作）
export { db };
